package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import model.Employee;

public class EmployeeDAO {
	Connection conn = null;
	ResultSet rs = null;
	PreparedStatement pst = null;

	// constructor
	public EmployeeDAO() {
		conn = DatabaseConnect.ConnectDb();
	}

	public Employee getUser(String userName, String password) {
		// Open Connection; Convert row to domain object; return user object;
		// return new Employee
		rs = null;
		Employee result = null;
		String sql = "select * from Employee where Username=? and Password=?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, userName);
			pst.setString(2, password);

			rs = pst.executeQuery();

			if (rs.next()) {
				//JOptionPane.showMessageDialog(null, "Username and Password is correct");
				result = new Employee(rs.getInt("SSN"), rs.getString("Name"),
						rs.getInt("Age"), rs.getString("Gender"),
						rs.getInt("Classification"));
			}

		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "LoginDao fault!!");
			return result;
		}
		return result;

	}

	public ArrayList<Employee> getUsers() {
		// Open Connection

		// GET User rows
		// convert rows to domain objects
		// Return users object list
		return null;
	}

	public ResultSet getEmployeeResultSet() {
		rs = null;
		String sql = "select * from Employee";
		try {
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			
			// table_employee.setModel(DbUtils.resultSetToTableModel(rs));
			return rs;
		} catch (SQLException ex) {
			JOptionPane.showMessageDialog(null, "EmployeeDao empty resultset");
		}
		return rs;
	}

	public void updateEmployee(String ssn, String name, String age, String gender, String u, String p, String c) {
		String sql = "Update Employee set Name=?, Age=? , " +
							"Gender=? , Username=? , " +
							"Password=?, Classification=?" +
							"Where SSN=?" ;
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, name); pst.setInt(2,Integer.parseInt(age));
			pst.setString(3, gender); pst.setString(4, u);
			pst.setString(5, p); pst.setInt(6, Integer.parseInt(c));
			pst.setInt(7, Integer.parseInt(ssn));
			
			pst.executeUpdate();
			pst.close();
		} catch (SQLException ex2) {
			System.out.println("EmployeeDao, updateEmployee exception!!");
		}		
	}

	public void newEmployee(String ssn, String name, String age, String gender, String u, String p, String c) {
		String sql = "insert into Employee (SSN, Name, Age, Gender, Username, Password, Classification) values (?,?,?,?,?,?,?)";
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, Integer.parseInt(ssn));
			pst.setString(2, name); pst.setInt(3,Integer.parseInt(age));
			pst.setString(4, gender); pst.setString(5, u);
			pst.setString(6, p); pst.setInt(7, Integer.parseInt(c));
			
			pst.executeUpdate();
			pst.close();
		} catch (Exception e) {
			System.out.println("EmployeeDao, insertEmployee exception!");
		}
	}

	public void deleteEmployee(String ssn) {
		String sql = "delete from Employee where SSN=?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, Integer.parseInt(ssn));
			pst.executeUpdate();
			pst.close();
		} catch (Exception e) {
			System.out.println("EmployeeDao, deleteEmployee exception!");
		}
		
	}
	
	

}
